const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectAuthList = async function() {
    let sql =  " SELECT "+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name  "+
                "FROM "+
                "	rc_major "+
                "WHERE "+
                "	rc_major.major_is_delete = 0 ";
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql);
    conn.release();
    return result
}

exports.selectList = async function (pageno,pagesize,major,department) {
    let sql =   " SELECT "+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name,  "+
                " 	rc_department.department_id,  "+
                " 	rc_department.department_name "+
                " FROM "+
                " 	rc_major "+
                " 	LEFT JOIN "+
                " 	rc_department "+
                " 	ON  "+
                " 		rc_major.major_department_id = rc_department.department_id "+
                " WHERE "+
                " 	rc_major.major_is_delete = 0 ";
    let params = [];
    if(major!=null&&major!=""){
        sql += "AND rc_major.major_name like ? "
        params.push( `%${major}%` );
    }
    if(department!=null&&department!=""){
        sql += "AND rc_department.department_name like ? "
        params.push( `%${department}%` );
    }
    
    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.selectById = async function (department_id) {
    let sql =   "SELECT"+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name,  "+
                " 	rc_major.major_department_id,  "+
                " 	rc_major.major_is_delete  "+
                " FROM "+
                " 	rc_major "+
                "WHERE "+
                "	rc_major.major_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[department_id]);
    conn.release();
    
    return result[0];
}

exports.selectByNameAndCondition = async function (major_name,major_id,major_is_delete) {
    let sql =   "SELECT"+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name,  "+
                " 	rc_major.major_department_id,  "+
                " 	rc_major.major_is_delete  "+
                " FROM "+
                " 	rc_major "+
                "WHERE "+
                "	rc_major.major_name = ? ";

    let params = [major_name];
    if(major_id!=null&&major_id!=""){
        sql += "AND rc_major.major_id != ? ";
        params.push( major_id );
    }
    if(major_is_delete!=null&&major_is_delete!=""){
        sql += "AND rc_major.major_is_delete = ? ";
        params.push( major_is_delete );
    }

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();
    
    return result[0];
}

exports.insert = async function (major_name,major_department_id,state=0) {
    let sql =  "insert into rc_major (major_name,major_department_id,major_is_delete) values (?,?,?)";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[major_name,major_department_id,state]);
    conn.release();
    
    return changedRows!=0;
}

exports.updateByDid = async function (major_name,major_department_id,major_id,state=0) {
    let sql =  "UPDATE rc_major SET major_name = ? , major_department_id = ? , major_is_delete = ? WHERE major_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[major_name,major_department_id,state,major_id]);
    conn.release();
    
    return changedRows!=0;
}

exports.deleteByDid = async function (major_id) {
    let sql =  "UPDATE rc_major SET major_is_delete = 1 WHERE major_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[major_id]);
    conn.release();
    
    return changedRows!=0;
}

